USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0013_CABECERA_BASE]    Script Date: 11/24/2011 11:37:10 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('WASP_M0013_CABECERA_BASE') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_M0013_CABECERA_BASE]
GO

USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0013_CABECERA_BASE]    Script Date: 11/24/2011 11:37:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[WASP_M0013_CABECERA_BASE] (@FECHA_INICIO CHAR(8), @FECHA_TERMINO AS CHAR(8)) AS
BEGIN

    SELECT DISTINCT 'D' AS TipoRegistro
              , NULL AS PeriodoProduccion
              , NULL AS FechaEnvio, '1' AS NroCorrelativoArchivo
              , '2' AS TipoMovimiento
              , CONVERT(varchar, a.aofe99, 112) AS FechaAnulacion
              , c.cod_seg AS NroProducto
              , '00000000' AS NroPoliza
              , a.aosuc AS AGENCOLOC
              , a.aooper AS NroCredito
              , SUBSTRING(b.Pfndoc, 1, LEN(b.Pfndoc) - 1) AS RutAsegurado
              , SUBSTRING(Pfndoc, LEN(Pfndoc), 1) AS DvRutAsegurado
              , RTRIM(Pfnom1)+ ' ' + RTRIM(b.Pfnom2) + ' ' + RTRIM(b.Pfape1) + ' ' + RTRIM(b.Pfape2) AS NombreAsegurado
              , totCuotas AS NroCuotas
              , 0 AS PrimaNetaCaja
              , CASE 
                  WHEN (totCuotas BETWEEN 3 AND 24) THEN CAST((0.6700) AS decimal(10, 4)) 
                  WHEN (totCuotas BETWEEN 25 AND 36) THEN CAST((1.2100) AS decimal(10, 4)) 
                  WHEN (totCuotas BETWEEN 37 AND 60) THEN CAST((1.7100) AS decimal(10, 4)) 
                  ELSE 0 
                END AS PrimaBrutaCaja
              , c.val_seg AS PrimaBrutaPeso
    into  dbo.cabecera_producion_prueba          
    FROM dbo.fsd010 AS a WITH (nolock) 
        CROSS JOIN dbo.fsd002 AS b WITH (nolock) 
        CROSS JOIN dbo.cabecera_seguro_asisten_penIP AS c 
        CROSS JOIN dbo.cabecera_produccion_Cuotas AS d 
        CROSS JOIN dbo.JT73109 AS e WITH (nolock)
    WHERE (e.JT73109FAP BETWEEN @FECHA_INICIO AND @FECHA_TERMINO) 
    AND (a.aosbop = 0) 
    AND (a.aooper <> 999999999) 
    AND (a.aocta <> 999999999) 
    AND (a.aocta = SUBSTRING(b.Pfndoc, 1, LEN(b.Pfndoc) - 1)) 
    AND (b.Pfndoc <> '') 
    AND (a.aocta = c.ppcta) 
    AND (a.aooper = c.ppoper) 
    AND (a.aocta = d.ppcta) 
    AND (a.aooper = d.ppoper) 
    AND (a.aosuc = d.ppsuc) 
    AND (a.aooper = e.JT73109FPA) 
    AND (a.aosuc = e.JT73109SUC) 
    AND (NOT EXISTS (SELECT *
                     FROM dbo.as_desistidos AS anu
                     WHERE (a.aocta = anu.hcta) AND (a.aooper = anu.hoper) AND (a.aosuc = anu.hsucur)))
END                                
GO


